﻿using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using YanForum.Engine.ViewModels;
using YanForum.Engine.Services;
using System;
using System.Collections.Generic;

namespace YanForum.Engine.Services
{
    public static class Account
    {
        static string connectionString = BaseService.ConnectionString;

        public static UserItem GetUserByEmail(string email)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetUserByEmail", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@email", email);
                var reader = cmd.ExecuteReader();

                var model = new UserItem();

                while (reader.Read())
                {
                    model.UserId = (int)reader["UserId"];
                    model.UserName = reader["UserName"].ToString();
                    model.RoleId = int.Parse(reader["RoleId"].ToString());
                    model.RoleName = reader["RoleName"].ToString();
                    model.Email = email;
                    model.FullName = reader["FullName"].ToString();
                    model.Avatar = reader["Avatar"].ToString();
                    model.Folder = reader["Folder"].ToString();
                    model.ImagePath = reader["Folder"].ToString() + "/" + reader["Avatar"].ToString();
                }
                reader.Close();

                return model;
            }
        }

        public static UserItem GetUserByUserId(int id)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetUserByUserId", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@userId", id);
                var reader = cmd.ExecuteReader();

                var model = new UserItem();

                while (reader.Read())
                {
                    model.UserId = id;
                    model.UserName = reader["UserName"].ToString();
                    model.RoleId = int.Parse(reader["RoleId"].ToString());
                    model.RoleName = reader["RoleName"].ToString();
                    model.Email = reader["Email"].ToString();
                    model.FullName = reader["FullName"].ToString();
                    model.Avatar = reader["Avatar"].ToString();
                    model.Folder = reader["Folder"].ToString();
                    model.ImagePath = reader["Folder"].ToString() + "/" + reader["Avatar"].ToString();
                }
                reader.Close();

                return model;
            }
        }

        public static void UpdateUserById(UserItem model)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                string fullName = !string.IsNullOrEmpty(model.FullName) ? model.FullName : " ";
                conn.Open();
                SqlCommand cmd = new SqlCommand("UpdateUserById", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@userId", model.UserId);
                cmd.Parameters.AddWithValue("@fullName", fullName);
                cmd.Parameters.AddWithValue("@avatar", model.Avatar);
                cmd.Parameters.AddWithValue("@folder", model.Folder);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        //by Tuan
        public static int CountAllUser()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("CountAllUser", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                var reader = cmd.ExecuteReader();
                int totalCount = 0;

                while (reader.Read())
                {
                    totalCount = (int)reader["TotalCount"];
                }
                reader.Close();

                return totalCount;
            }
        }

        public static List<RoleItem> GetListRole()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetListRole", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                var reader = cmd.ExecuteReader();

                var model = new List<RoleItem>();

                while (reader.Read())
                {
                    var item = new RoleItem
                    {
                        RoleId = int.Parse(reader["RoleId"].ToString()),
                        RoleName = reader["RoleName"].ToString()
                    };

                    model.Add(item);
                }
                reader.Close();

                return model;
            }
        }
        //by Tuan
        public static UserIndex GetListUser(int pageSize, int pageIndex, UserOptions options)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetListUser", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                options.RoleId = !options.RoleId.HasValue ? -1 : options.RoleId;
                options.Search = !string.IsNullOrEmpty(options.Search) ? options.Search : "";

                cmd.Parameters.AddWithValue("@pageSize", pageSize);
                cmd.Parameters.AddWithValue("@pageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@roleId", options.RoleId);
                cmd.Parameters.AddWithValue("@search", options.Search);
                var reader = cmd.ExecuteReader();

                var model = new UserIndex();
                model.ListUser = new System.Collections.Generic.List<UserItem>();
                model.PageIndex = pageIndex;
                model.PageSize = pageSize;
                model.Options = options;

                reader.Read();
                int total = (int)reader["TotalCount"];
                model.TotalCount = total;

                // gets the second
                reader.NextResult();
                while (reader.Read())
                {
                    var item = new UserItem
                    {
                        UserId = int.Parse(reader["UserId"].ToString()),
                        UserName = reader["UserName"].ToString(),
                        FullName = reader["FullName"].ToString(),
                        Folder = reader["Folder"].ToString(),
                        Email = reader["Email"].ToString(),
                        Avatar = reader["Avatar"].ToString(),
                        ImagePath = reader["Folder"].ToString() + "/" + reader["Avatar"].ToString(),
                        CrateDate = DateTime.Parse(reader["CreateDate"].ToString()),

                        RoleId = int.Parse(reader["RoleId"].ToString()),
                        RoleName = reader["RoleName"].ToString()
                    };

                    model.ListUser.Add(item);
                }
                reader.Close();

                return model;
            }
        }

        public static void UpdateUserRole(int userId, int roleId)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("UpdateUserRole", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.Parameters.AddWithValue("@roleId", roleId);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }
}